﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ZK.CoalYardTransManage.DBUtility
{
    public class Sqlite3Helper
    {
        public Sqlite3Helper()
        {

        }

        //数据库连接
        public static SQLiteConnection m_dbConnection;


        //static string path = @"c:\sqliteTest\sss.db";
        public static string path = "MyDatabase.db";
        /// <summary>
        /// 创建空数据库
        /// </summary>
        public static void CreateDB()
        {
            //string path = @"d:\sqliteTest\sss.sqlite";

            SQLiteConnection.CreateFile(path);
            //SQLiteConnection cn = new SQLiteConnection("data source=" + path);
            //cn.Open();
            //cn.Close();
        }
        //---删除数据库
        public static void DeleteDB()
        {
            string path = @"d:\test\123.sqlite";
            if (System.IO.File.Exists(path))
            {
                System.IO.File.Delete(path);
            }
        }
        /// <summary>
        /// 创建一个连接到指定数据库
        /// </summary>
        public static void connectToDatabase()
        {
            SQLiteConnection m_dbConnection = new SQLiteConnection("Data Source=" + path + ";Version=3;Pooling=true;FailIfMissing=false");
            if (System.IO.File.Exists(path))
            {
                bool a = true;
            }
            m_dbConnection.Open();
            m_dbConnection.Close();
        }
        /// <summary>
        /// 添加表
        /// </summary>
        public static void CreateTable(string sql)
        {
            //string path = @"d:\test\123.sqlite";
            SQLiteConnection m_dbConnection = new SQLiteConnection("Data Source=" + path + ";Version=3;Pooling=true;FailIfMissing=false");
            if (m_dbConnection.State != System.Data.ConnectionState.Open)
            {
                m_dbConnection.Open();
                SQLiteCommand cmd = new SQLiteCommand();
                cmd.Connection = m_dbConnection;
                //cmd.CommandText = "CREATE TABLE t1 (id,nane)";
                cmd.CommandText = sql;
                //cmd.CommandText = "CREATE TABLE IF NOT EXISTS t1(id varchar(4),score int)";
                cmd.ExecuteNonQuery();

                //string sql = "create table highscores (name varchar(20), score int)";
                //SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
                //command.ExecuteNonQuery();
            }
            m_dbConnection.Close();
        }
        /// <summary>
        /// 添加一行数据
        /// </summary>
        public static void AddRows(string sql)
        {
            SQLiteConnection m_dbConnection = new SQLiteConnection("Data Source=" + path + ";Version=3;Pooling=true;FailIfMissing=false");
            if (m_dbConnection.State != System.Data.ConnectionState.Open)
            {
                m_dbConnection.Open();
                SQLiteCommand cmd = new SQLiteCommand();
                cmd.Connection = m_dbConnection;
                //cmd.CommandText = "insert into t1 (id,nane) values (4,4)";
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();
            }
            m_dbConnection.Close();
        }

        /// <summary>
        /// 获取数据
        /// </summary>
        /// <returns></returns>
        public static DataSet GetData(string sql)
        {
            SQLiteConnection m_dbConnection = new SQLiteConnection("Data Source=" + path + ";Version=3;Pooling=true;FailIfMissing=false");
            DataSet ds = new DataSet();
            if (m_dbConnection.State != System.Data.ConnectionState.Open)
            {
                m_dbConnection.Open();
                SQLiteCommand cmd = m_dbConnection.CreateCommand();
                //cmd.CommandText = "SELECT id FROM t1";
                cmd.CommandText = sql;
                SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
                da.Fill(ds);
                da.Dispose();
                cmd.Dispose();



                //SQLiteCommand cmd = new SQLiteCommand();
                //cmd.Connection = m_dbConnection;
                //cmd.CommandText = "select * from t1";
                //var reader =  cmd.ExecuteReader();
                //while (reader.Read()) {
                //    //var aaa = reader;
                //    Console.WriteLine(reader["id"]);
                //}


            }
            m_dbConnection.Close();
            return ds;
        }

       
       
    }
}
